--    Author    : ChenErHao
--    Name      : DM.T_MTH_PANDECT_KPI_VALUE.HQL
--    Functions : 总览-核心指标值表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-22  ChenErHao       1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE DM.T_MTH_PANDECT_KPI_VALUE PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 

 SELECT 
  nvl(T1.AREA_NO_ID,9999) AREA_NO_ID,
  '01' KPI_ID,
  nvl(T1.KPI_VALUE,0) MTH_AMT,
  nvl(T2.KPI_VALUE,0) LAST_MTH_AMT,
  nvl(T3.KPI_VALUE,0) LAST_YEAR_AMT 
  FROM 
  (select  nvl(AREA_NO_ID,'450000') AREA_NO_ID,SUM(KPI_VALUE) KPI_VALUE from 
    (SELECT  
              BELONG_AREA_CODE   AREA_NO_ID,
              DEP_BALANCE  KPI_VALUE
   FROM EDW.DEPB A
   WHERE DATA_DATE='#V_DATA_DATE#' )t
   group by AREA_NO_ID
   grouping sets(AREA_NO_ID,()) )T1
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE  WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_1M_END_MONTH#' AND KPI_ID = '01') T2
    ON T1.AREA_NO_ID=T2.AREA_NO_ID
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_12M_END_MONTH#' AND KPI_ID = '01') T3
    ON T1.AREA_NO_ID=T3.AREA_NO_ID
    
UNION ALL
    
  SELECT 
  nvl(T1.AREA_NO_ID,9999) AREA_NO_ID,
  '02' KPI_ID,
  nvl(T1.KPI_VALUE,0) MTH_AMT,
  nvl(T2.KPI_VALUE,0) LAST_MTH_AMT,
  nvl(T3.KPI_VALUE,0) LAST_YEAR_AMT 
  FROM 
  (select  nvl(AREA_NO_ID,'450000') AREA_NO_ID,SUM(KPI_VALUE)/SUM(DEP_BALANCE) KPI_VALUE from 
        (SELECT 
              BELONG_AREA_CODE   AREA_NO_ID,
              DEP_BALANCE DEP_BALANCE,
              LOAN_OCCURRENCE_LEVEL  KPI_VALUE
   FROM EDW.DEPB A
     WHERE DATA_DATE='#V_DATA_DATE#')t
   group by AREA_NO_ID
   grouping sets(AREA_NO_ID,()) ) T1
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE  WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_1M_END_MONTH#' AND KPI_ID = '02') T2
    ON T1.AREA_NO_ID=T2.AREA_NO_ID
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_12M_END_MONTH#' AND KPI_ID = '02') T3
    ON T1.AREA_NO_ID=T3.AREA_NO_ID
    
UNION ALL
    
  SELECT 
  nvl(T1.AREA_NO_ID,9999) AREA_NO_ID,
  '03' KPI_ID,
  nvl(T1.KPI_VALUE,0) MTH_AMT,
  nvl(T2.KPI_VALUE,0) LAST_MTH_AMT,
  nvl(T3.KPI_VALUE,0) LAST_YEAR_AMT 
  FROM 
  (select  nvl(AREA_NO_ID,'450000') AREA_NO_ID,SUM(KPI_VALUE) KPI_VALUE from (SELECT 
              BELONG_AREA_CODE  AREA_NO_ID, LOAN_BALANCE  KPI_VALUE
   FROM EDW.LOAB A
      WHERE DATA_DATE='#V_DATA_DATE#' )t
   group by AREA_NO_ID
   grouping sets((AREA_NO_ID),())) T1
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE  WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_1M_END_MONTH#' AND KPI_ID = '03') T2
    ON T1.AREA_NO_ID=T2.AREA_NO_ID
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_12M_END_MONTH#' AND KPI_ID = '03') T3
    ON T1.AREA_NO_ID=T3.AREA_NO_ID
    
UNION ALL
    
  SELECT 
  nvl(T1.AREA_NO_ID,9999) AREA_NO_ID,
  '04' KPI_ID,
  nvl(T1.KPI_VALUE,0) MTH_AMT,
  nvl(T2.KPI_VALUE,0) LAST_MTH_AMT,
  nvl(T3.KPI_VALUE,0) LAST_YEAR_AMT 
  FROM 
  (select  nvl(AREA_NO_ID,'450000') AREA_NO_ID,SUM(KPI_VALUE)/SUM(LOAN_BALANCE) KPI_VALUE from 
  (SELECT
         BELONG_AREA_CODE  AREA_NO_ID,
         LOAN_OCCURRENCE_LEVEL   KPI_VALUE,
         LOAN_BALANCE LOAN_BALANCE
   FROM EDW.LOAB A
      WHERE DATA_DATE='#V_DATA_DATE#' )t
   group by AREA_NO_ID
   grouping sets((AREA_NO_ID),())) T1
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE  WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_1M_END_MONTH#' AND KPI_ID = '04') T2
    ON T1.AREA_NO_ID=T2.AREA_NO_ID
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_12M_END_MONTH#' AND KPI_ID = '04') T3
    ON T1.AREA_NO_ID=T3.AREA_NO_ID
    
UNION ALL
     
  SELECT
  '450000' AREA_NO_ID,
  '05' KPI_ID,
  nvl(sum(T1.dep_amount),0) MTH_AMT,
  nvl(sum(T2.KPI_VALUE),0) LAST_MTH_AMT,
  nvl(sum(T3.KPI_VALUE),0) LAST_YEAR_AMT 
  FROM 
 DM.MID_T_MTH_DEP_RATE_D011013 T1 
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE  WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_1M_END_MONTH#' AND KPI_ID = '05') T2
    ON T1.data_area_code=T2.AREA_NO_ID
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_12M_END_MONTH#' AND KPI_ID = '05') T3
    ON T1.data_area_code=T3.AREA_NO_ID
    where t1.data_month='#V_DATA_MONTH#'
    AND LENGTH(T1.DATA_ORG_CODE)=3

    
UNION ALL    

SELECT
  nvl(T1.data_area_code,9999) AREA_NO_ID,
  '05' KPI_ID,
  nvl(sum(T1.dep_amount),0) MTH_AMT,
  nvl(sum(T2.KPI_VALUE),0) LAST_MTH_AMT,
  nvl(sum(T3.KPI_VALUE),0) LAST_YEAR_AMT 
  FROM 
 DM.MID_T_MTH_DEP_RATE_D011013 T1 
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE  WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_1M_END_MONTH#' AND KPI_ID = '05') T2
    ON T1.data_area_code=T2.AREA_NO_ID
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_12M_END_MONTH#' AND KPI_ID = '05') T3
    ON T1.data_area_code=T3.AREA_NO_ID
    where t1.data_month='#V_DATA_MONTH#'
    AND LENGTH(T1.DATA_ORG_CODE)=3
    group by T1.data_area_code
    
UNION ALL  
    
  SELECT
  '450000' AREA_NO_ID,
  '06' KPI_ID,
  case when nvl(sum(T1.dep_amount),0) = 0 then 0 else nvl(sum(T1.dep_amount_amt),0)/nvl(sum(T1.dep_amount),0) end as MTH_AMT,
  nvl(T2.KPI_VALUE,0) LAST_MTH_AMT,
  nvl(T3.KPI_VALUE,0) LAST_YEAR_AMT 
  FROM 
 DM.MID_T_MTH_DEP_RATE_D011013 T1 
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE  WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_1M_END_MONTH#' AND KPI_ID = '06' AND AREA_NO_ID='450000') T2
    ON 1=1
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_12M_END_MONTH#' AND KPI_ID = '06' AND AREA_NO_ID='450000') T3
    ON 1=1
    where t1.data_month='#V_DATA_MONTH#'
    AND LENGTH(t1.DATA_ORG_CODE)=3
    group by T2.KPI_VALUE,T3.KPI_VALUE
    
UNION ALL

SELECT
  nvl(T1.data_area_code,9999) AREA_NO_ID,
  '06' KPI_ID,
  case when nvl(sum(T1.dep_amount),0) = 0 then 0 else nvl(sum(T1.dep_amount_amt),0)/nvl(sum(T1.dep_amount),0) end as MTH_AMT,
  nvl(T2.KPI_VALUE,0) LAST_MTH_AMT,
  nvl(T3.KPI_VALUE,0) LAST_YEAR_AMT 
  FROM 
 DM.MID_T_MTH_DEP_RATE_D011013 T1 
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE  WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_1M_END_MONTH#' AND KPI_ID = '06') T2
    ON T1.data_area_code=T2.AREA_NO_ID
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_12M_END_MONTH#' AND KPI_ID = '06') T3
    ON T1.data_area_code=T3.AREA_NO_ID
    where t1.data_month='#V_DATA_MONTH#'
    AND LENGTH(t1.DATA_ORG_CODE)=3
    group by T1.data_area_code,T2.KPI_VALUE,T3.KPI_VALUE
    
UNION ALL
    
  SELECT 
  T1.AREA_NO_ID AREA_NO_ID,
  '07' KPI_ID,
  T1.KPI_VALUE MTH_AMT,
  T2.KPI_VALUE LAST_MTH_AMT,
  T3.KPI_VALUE LAST_YEAR_AMT 
  FROM 
  (select  nvl(AREA_NO_ID,'450000') AREA_NO_ID,SUM(KPI_VALUE) KPI_VALUE from (SELECT 
              BELONG_AREA_CODE  AREA_NO_ID,LOAN_OCCURRENCE  KPI_VALUE
   FROM EDW.LOAF A
      WHERE DATA_DATE='#V_DATA_DATE#' 
      and LOAN_GRANT_RECOVER ='1'
      )t
   group by AREA_NO_ID
   grouping sets((AREA_NO_ID),()) ) T1
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_1M_END_MONTH#' AND KPI_ID = '07') T2
    ON T1.AREA_NO_ID=T2.AREA_NO_ID
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_12M_END_MONTH#' AND KPI_ID = '07') T3
    ON T1.AREA_NO_ID=T3.AREA_NO_ID
    
UNION ALL  
    
  SELECT 
  T1.AREA_NO_ID AREA_NO_ID,
  '08' KPI_ID,
  T1.KPI_VALUE MTH_AMT,
  T2.KPI_VALUE LAST_MTH_AMT,
  T3.KPI_VALUE LAST_YEAR_AMT 
  FROM 
  (select  nvl(AREA_NO_ID,'450000') AREA_NO_ID,SUM(KPI_VALUE)/SUM(LOAN_OCCURRENCE) KPI_VALUE from (SELECT
              BELONG_AREA_CODE  AREA_NO_ID,
         LOAN_OCCURRENCE_LEVEL   KPI_VALUE,LOAN_OCCURRENCE LOAN_OCCURRENCE
   FROM EDW.LOAF A
      WHERE DATA_DATE='#V_DATA_DATE#'
      and LOAN_GRANT_RECOVER ='1'
      )t
   group by AREA_NO_ID
   grouping sets((AREA_NO_ID),()) ) T1
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_1M_END_MONTH#' AND KPI_ID = '08') T2
    ON T1.AREA_NO_ID=T2.AREA_NO_ID
  LEFT JOIN (SELECT AREA_NO_ID,MTH_AMT KPI_VALUE FROM DM.T_MTH_PANDECT_KPI_VALUE WHERE FISCAL_MTH_DIMNSN_ID= '#V_LAST_12M_END_MONTH#' AND KPI_ID = '08') T3
    ON T1.AREA_NO_ID=T3.AREA_NO_ID

UNION ALL

 SELECT --下面统计市级数据
  nvl(t.AREA_NO_ID_2,9999) as AREA_NO_ID,
  '01' KPI_ID,
  SUM(nvl(case when a.DATA_DATE='#V_DATA_DATE#' then a.DEP_BALANCE else 0 end,0)) as MTH_AMT,
  SUM(nvl(case when a.DATA_DATE='#V_LAST_1M_END_DATE#' then a.DEP_BALANCE else 0 end,0)) as LAST_MTH_AMT,
  SUM(nvl(case when a.DATA_DATE='#V_LAST_12M_END_DATE#' then a.DEP_BALANCE else 0 end,0)) as LAST_YEAR_AMT 
  FROM  dmcode.t_area_code t
  left join EDW.DEPB a on t.area_no_id=a.BELONG_AREA_CODE and a.DATA_DATE in ('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#')
  where level=3
 group by t.AREA_NO_ID_2
 
UNION ALL

  SELECT 
  nvl(t.AREA_NO_ID_2,9999) as AREA_NO_ID,
  '02' KPI_ID,
  SUM(nvl(case when a.DATA_DATE='#V_DATA_DATE#' then a.LOAN_OCCURRENCE_LEVEL else 0 end,0))/SUM(nvl(case when a.DATA_DATE='#V_DATA_DATE#' then a.DEP_BALANCE else 0 end,0)) as MTH_AMT,
  SUM(nvl(case when a.DATA_DATE='#V_LAST_1M_END_DATE#' then a.LOAN_OCCURRENCE_LEVEL else 0 end,0))/SUM(nvl(case when a.DATA_DATE='#V_LAST_1M_END_DATE#' then a.DEP_BALANCE else 0 end,0)) as LAST_MTH_AMT,
  SUM(nvl(case when a.DATA_DATE='#V_LAST_12M_END_DATE#' then a.LOAN_OCCURRENCE_LEVEL else 0 end,0))/SUM(nvl(case when a.DATA_DATE='#V_LAST_12M_END_DATE#' then a.DEP_BALANCE else 0 end,0)) as LAST_YEAR_AMT 
  FROM  dmcode.t_area_code t
  left join EDW.DEPB a on t.area_no_id=a.BELONG_AREA_CODE and a.DATA_DATE in ('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#')
  where level=3
 group by t.AREA_NO_ID_2
 
UNION ALL

 SELECT 
  nvl(t.AREA_NO_ID_2,9999) as AREA_NO_ID,
  '03' KPI_ID,
  SUM(nvl(case when a.DATA_DATE='#V_DATA_DATE#' then a.LOAN_BALANCE else 0 end,0)) as MTH_AMT,
  SUM(nvl(case when a.DATA_DATE='#V_LAST_1M_END_DATE#' then a.LOAN_BALANCE else 0 end,0)) as LAST_MTH_AMT,
  SUM(nvl(case when a.DATA_DATE='#V_LAST_12M_END_DATE#' then a.LOAN_BALANCE else 0 end,0)) as LAST_YEAR_AMT 
  FROM  dmcode.t_area_code t
  left join EDW.LOAB a on t.area_no_id=a.BELONG_AREA_CODE and a.DATA_DATE in ('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#')
  where level=3
 group by t.AREA_NO_ID_2

UNION ALL

  SELECT 
  nvl(t.AREA_NO_ID_2,9999) as AREA_NO_ID,
  '04' KPI_ID,
  SUM(nvl(case when a.DATA_DATE='#V_DATA_DATE#' then a.LOAN_OCCURRENCE_LEVEL else 0 end,0))/SUM(nvl(case when a.DATA_DATE='#V_DATA_DATE#' then a.LOAN_BALANCE else 0 end,0)) as MTH_AMT,
  SUM(nvl(case when a.DATA_DATE='#V_LAST_1M_END_DATE#' then a.LOAN_OCCURRENCE_LEVEL else 0 end,0))/SUM(nvl(case when a.DATA_DATE='#V_LAST_1M_END_DATE#' then a.LOAN_BALANCE else 0 end,0)) as LAST_MTH_AMT,
  SUM(nvl(case when a.DATA_DATE='#V_LAST_12M_END_DATE#' then a.LOAN_OCCURRENCE_LEVEL else 0 end,0))/SUM(nvl(case when a.DATA_DATE='#V_LAST_12M_END_DATE#' then a.LOAN_BALANCE else 0 end,0)) as LAST_YEAR_AMT 
  FROM  dmcode.t_area_code t
  left join EDW.LOAB a on t.area_no_id=a.BELONG_AREA_CODE and a.DATA_DATE in ('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#')
  where level=3
 group by t.AREA_NO_ID_2
 
UNION ALL

 SELECT 
  nvl(t.AREA_NO_ID_2,9999) as AREA_NO_ID,
  '05' KPI_ID,
  SUM(nvl(case when a.DATA_MONTH='#V_DATA_MONTH#' then a.dep_amount else 0 end,0)) as MTH_AMT,
  SUM(nvl(case when a.DATA_MONTH='#V_LAST_1M_END_MONTH#' then a.dep_amount else 0 end,0)) as LAST_MTH_AMT,
  SUM(nvl(case when a.DATA_MONTH='#V_LAST_12M_END_MONTH#' then a.dep_amount else 0 end,0)) as LAST_YEAR_AMT 
  FROM  dmcode.t_area_code t
  left join DM.MID_T_MTH_DEP_RATE_D011013 a on t.area_no_id=a.data_area_code AND LENGTH(DATA_ORG_CODE)=3 and a.DATA_MONTH in ('#V_DATA_MONTH#','#V_LAST_1M_END_MONTH#','#V_LAST_12M_END_MONTH#')
  where level=3
 group by t.AREA_NO_ID_2 

UNION ALL

 SELECT 
  nvl(t.AREA_NO_ID_2,9999) as AREA_NO_ID,
  '06' KPI_ID,
  SUM(nvl(case when a.DATA_MONTH='#V_DATA_MONTH#' then a.dep_amount_amt else 0 end,0))/SUM(nvl(case when a.DATA_MONTH='#V_DATA_MONTH#' then a.dep_amount else 0 end,0)) as MTH_AMT,
  SUM(nvl(case when a.DATA_MONTH='#V_LAST_1M_END_MONTH#' then a.dep_amount_amt else 0 end,0))/SUM(nvl(case when a.DATA_MONTH='#V_LAST_1M_END_MONTH#' then a.dep_amount else 0 end,0)) as LAST_MTH_AMT,
  SUM(nvl(case when a.DATA_MONTH='#V_LAST_12M_END_MONTH#' then a.dep_amount_amt else 0 end,0))/SUM(nvl(case when a.DATA_MONTH='#V_LAST_12M_END_MONTH#' then a.dep_amount else 0 end,0)) as LAST_YEAR_AMT 
  FROM  dmcode.t_area_code t
  left join DM.MID_T_MTH_DEP_RATE_D011013 a on t.area_no_id=a.data_area_code AND LENGTH(DATA_ORG_CODE)=3 and a.DATA_MONTH in ('#V_DATA_MONTH#','#V_LAST_1M_END_MONTH#','#V_LAST_12M_END_MONTH#')
  where level=3
 group by t.AREA_NO_ID_2

UNION ALL

 SELECT 
  nvl(t.AREA_NO_ID_2,9999) as AREA_NO_ID,
  '07' KPI_ID,
  SUM(nvl(case when a.DATA_DATE='#V_DATA_DATE#' then a.LOAN_OCCURRENCE else 0 end,0)) as MTH_AMT,
  SUM(nvl(case when a.DATA_DATE='#V_LAST_1M_END_DATE#' then a.LOAN_OCCURRENCE else 0 end,0)) as LAST_MTH_AMT,
  SUM(nvl(case when a.DATA_DATE='#V_LAST_12M_END_DATE#' then a.LOAN_OCCURRENCE else 0 end,0)) as LAST_YEAR_AMT 
  FROM  dmcode.t_area_code t
  left join EDW.LOAF a on t.area_no_id=a.BELONG_AREA_CODE and LOAN_GRANT_RECOVER ='1' and a.DATA_DATE in ('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#')
  where level=3
 group by t.AREA_NO_ID_2

UNION ALL

  SELECT 
  nvl(t.AREA_NO_ID_2,9999) as AREA_NO_ID,
  '08' KPI_ID,
  SUM(nvl(case when a.DATA_DATE='#V_DATA_DATE#' then a.LOAN_OCCURRENCE_LEVEL else 0 end,0))/SUM(nvl(case when a.DATA_DATE='#V_DATA_DATE#' then a.LOAN_OCCURRENCE else 0 end,0)) as MTH_AMT,
  SUM(nvl(case when a.DATA_DATE='#V_LAST_1M_END_DATE#' then a.LOAN_OCCURRENCE_LEVEL else 0 end,0))/SUM(nvl(case when a.DATA_DATE='#V_LAST_1M_END_DATE#' then a.LOAN_OCCURRENCE else 0 end,0)) as LAST_MTH_AMT,
  SUM(nvl(case when a.DATA_DATE='#V_LAST_12M_END_DATE#' then a.LOAN_OCCURRENCE_LEVEL else 0 end,0))/SUM(nvl(case when a.DATA_DATE='#V_LAST_12M_END_DATE#' then a.LOAN_OCCURRENCE else 0 end,0)) as LAST_YEAR_AMT 
  FROM  dmcode.t_area_code t
  left join EDW.LOAF a on t.area_no_id=a.BELONG_AREA_CODE and LOAN_GRANT_RECOVER ='1' and a.DATA_DATE in ('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#')
  where level=3
 group by t.AREA_NO_ID_2
;
 